<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Shortrecord extends Model
{
    protected $table = 'short_record_';
    protected $primaryKey = 'id';

    public function __construct(array $custb=[])
    {
        if (empty($custb)) {
            $this->table = 'short_record_'.(date("m",time())%4);
        } else {
            $this->table = 'short_record_'.$custb["tbnum"];
        }
    }

    public function creatShortRecord($record_id,$short_id,$code,$system,$browser,$device,$ip)
    {
        return self::insert(["record_id"=>$record_id,"short_id"=>$short_id,"code"=>$code,"system"=>$system,"browser"=>$browser,"device"=>$device,"ip"=>$ip,"created_at"=>time()]);
    }

    public function creatWorkLinkRecordBatch($linksRecords)
    {
        return self::insert($linksRecords);
    }

    public function updateWorkLinkRecordPressTime($record_id,$qrcode_press_time)
    {
        return self::where("record_id",$record_id)->update(["qrcode_press_time"=>$qrcode_press_time]);
    }

    public function updateWorkLinkRecordShowTime($record_id,$qrcode_show_time,$kefu_time)
    {
        return self::where("record_id",$record_id)->update(["qrcode_show_time"=>$qrcode_show_time,"kefu_time"=>$kefu_time]);
    }

    public function shortRecordLists($short_id,$date_start,$date_end)
    {
        $query = self::where('short_id',$short_id);
        if (!empty($date_start)) $query->whereBetween('created_at',[$date_start,$date_end]);
        return $query->orderBy("id","DESC")->paginate(10);
    }

    public function shortRecordListSample($short_id,$date_start,$date_end,$offset,$limit)
    {
        $query = self::where('short_id', $short_id);
        if (!empty($date_start)) $query->whereBetween($this->table.'.created_at',[$date_start,$date_end]);
        return $query->offset($offset)->limit($limit)->orderBy("id","DESC")->get();
    }

    public function shortRecordSampleCount($short_id,$date_start,$date_end)
    {
        $query = self::where('short_id', $short_id);
        if (!empty($date_start)) $query->whereBetween('created_at',[$date_start,$date_end]);
        return $query->count("id");
    }

    public function getWorkLinkRecordDetial($record_id)
    {
        return self::where("record_id", $record_id)->first();
    }

    public function kefuLinkRecordBackPlatfrom($short_id,$record_id)
    {
        return self::select("kefu_time","back_platfrom","callback_status")->where("short_id",$short_id)->where("record_id",$record_id)->first();
    }

    public function getSystemDatas($short_id)
    {
        return self::select(DB::raw('count(id) as system_count, system'))
                     ->where('short_id', $short_id)
                     ->distinct('ip')
                     ->groupBy('system')
                     ->get();
    }

    public function getBrowserDatas($short_id)
    {
        return self::select(DB::raw('count(id) as browser_count, browser'))
                     ->where('short_id', $short_id)
                     ->distinct('ip')
                     ->groupBy('browser')
                     ->get();
    }

    public function getDeviceDatas($short_id)
    {
        return self::select(DB::raw('count(id) as device_count, device'))
                     ->where('short_id', $short_id)
                     ->distinct('ip')
                     ->groupBy('device')
                     ->get();
    }

    public function getProvinceDatas($short_id)
    {
        return self::select(DB::raw('count(id) as province_count, province'))
                     ->where('short_id', $short_id)
                     ->distinct('ip')
                     ->groupBy('province')
                     ->get();
    }

    public function getDailyCount($short_id,$keyword,$start_time,$end_time)
    {
        return self::where('short_id', $short_id)
                     ->where($keyword,">",1)
                     ->whereBetween("created_at",[$start_time,$end_time])
                     ->count();
    }

    public function getDailyDirectCount($short_id,$start_time,$end_time)
    {
        return self::where('short_id', $short_id)
                     ->where("web_time",1)
                     ->where("qrcode_show_time",">",1)
                     ->whereBetween("created_at",[$start_time,$end_time])
                     ->count();
    }

    public function getDefaultDatas($short_id,$start_time,$end_time,$datebatch)
    {
        if ($datebatch == "day") {
            $unit = "%Y-%m-%d";
        } else {
            $unit = "%H";
        }
        return self::select(DB::raw('count(id) as count, DATE_FORMAT(FROM_UNIXTIME(created_at),"'.$unit.'") as `date`,status'))
                     ->where('short_id', $short_id)
                     ->whereBetween('created_at', [$start_time,$end_time])
                     ->distinct('ip')
                     ->groupBy('date')
                     ->groupBy('status')
                     ->get();
    }

    public function getKfRecordHour($short_id,$keyword,$created_at)
    {
        return DB::select('select count("id") as count,FROM_UNIXTIME(created_at,"%H") as hour from '.$this->table.' where short_id = "'.$short_id.'" and '.$keyword.' > 1 and FROM_UNIXTIME(created_at,"%Y-%m-%d") = "'.$created_at.'" group by hour');
    }

    public function rdirectKfRecordHour($short_id,$created_at)
    {
        return DB::select('select count("id") as count,FROM_UNIXTIME(created_at,"%H") as hour from '.$this->table.' where short_id = "'.$short_id.'" and web_time = 1 and kefu_time > 1 and FROM_UNIXTIME(created_at,"%Y-%m-%d") = "'.$created_at.'" group by hour');
    }

    public function getLinkExportCount($short_id,$start_time,$end_time)
    {
        return self::where('short_id',$short_id)
                     ->whereBetween("created_at",[$start_time,$end_time])
                     ->count();
    }
}
